﻿'Description:班次维护后台方法
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Zhangrj 
'create Date:20220510
'Rewriter:
'Rewrite Date:
Imports DatabaseAccess.DBUtility
Imports BusinessRule.DTO
Imports System.Text
Imports External.APICalling
Imports System.Linq

Public Class WorkShiftBusiness
    ''' <summary>
    ''' 获取班次信息（分页）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkShiftListByPage(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim strSql As String = "SELECT ws.*,DECODE(ws.isuse,'0','是','否')  isusename FROM workshift ws"
        If para.Keys.Contains("workShiftName") Then
            If Not String.IsNullOrWhiteSpace(para("workShiftName")) Then
                strSql += String.Format(" WHERE ws.workshiftname LIKE '%{0}%'", para("workShiftName"))
            End If
        End If
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("PageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql.ToString()
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取班次详细加工时间
    ''' </summary>
    ''' <param name="workshiftId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkShiftDetailInfo(ByVal workshiftId As String) As DataTable
        Dim strSql As String = "SELECT * FROM Workshiftdetail w WHERE w.workshiftid = '" + workshiftId + "' ORDER BY w.sequence"
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取有效的班次信息
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkShiftList() As DataTable
        Dim strSql As String = "SELECT * FROM workshift ws where ws.isuse = '0'"
        Return DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
    End Function

    ''' <summary>
    ''' 获取班次信息（通过班次名）
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkShiftInfo(ByVal workShiftName As String) As DataTable
        Dim strSql As String = String.Format("SELECT * FROM workshift ws where ws.workshiftname = '{0}'", workShiftName)
        Return DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
    End Function

    ''' <summary>
    ''' 保存班次数据（新增、更新、删除）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveWorkShiftData(ByVal para As DataRow, ByVal dtDetail As DataTable, ByVal employeeID As String) As uMESProcessResult
        Dim result = New uMESProcessResult()
        Try
            Dim sqlList As New ArrayList
            result = IsUserWorkShift(para) '保存前做数据验证
            If Not result.Result Then
                Return result
                Exit Function
            End If
            Dim strsql As New StringBuilder
            If para("type") = "add" Then '添加
                strsql.Clear()
                Dim workshiftId As String = Guid.NewGuid.ToString
                strsql.AppendLine("INSERT INTO workshift (WORKSHIFTID,WORKSHIFTNAME,STARTDATE,ENDDATE,ISUSE,NOTES,CREATEEMPLOYEEID,CREATEDATE) VALUES(")
                strsql.AppendFormat("'{0}',", workshiftId)
                strsql.AppendFormat("'{0}',", para("WORKSHIFTNAME"))
                strsql.AppendFormat("'{0}',", para("STARTDATE"))
                strsql.AppendFormat("'{0}',", para("ENDDATE"))
                strsql.AppendFormat("'{0}',", para("ISUSE"))
                strsql.AppendFormat("'{0}',", para("NOTES"))
                strsql.AppendFormat("'{0}',", employeeID)
                strsql.AppendFormat("{0})", "sysdate")
                sqlList.Add(strsql.ToString)
                For Each dr As DataRow In dtDetail.Rows
                    strsql.Clear()
                    strsql.AppendLine("INSERT INTO workshiftdetail (WORKSHIFTDETAILID,WORKSHIFTID,STARTDATE,ENDDATE,SEQUENCE,MINS) VALUES(sys_guid(),")
                    strsql.AppendFormat("'{0}',", workshiftId)
                    strsql.AppendFormat("'{0}',", dr("STARTDATE"))
                    strsql.AppendFormat("'{0}',", dr("ENDDATE"))
                    strsql.AppendFormat("'{0}',", dr("SEQUENCE"))
                    strsql.AppendFormat("'{0}')", dr("MINS"))
                    sqlList.Add(strsql.ToString)
                Next
                DatabaseHelper.ExecuteSqlTran(sqlList)
                result.Result = True
                result.Message = "添加成功"
            ElseIf para("type") = "update" Then '更新
                strsql.Clear()
                strsql.AppendFormat("DELETE FROM workshiftdetail wsd WHERE wsd.WORKSHIFTID = '{0}'", para("WORKSHIFTID"))
                sqlList.Add(strsql.ToString)
                strsql.Clear()
                strsql.AppendLine("UPDATE workshift ws SET ")
                strsql.AppendFormat("ws.workshiftname ='{0}',", para("WORKSHIFTNAME"))
                strsql.AppendFormat("ws.startdate='{0}',", para("STARTDATE"))
                strsql.AppendFormat("ws.enddate='{0}',", para("ENDDATE"))
                strsql.AppendFormat("ws.isuse='{0}',", para("ISUSE"))
                strsql.AppendFormat("ws.notes='{0}'", para("NOTES"))
                strsql.AppendFormat(" WHERE ws.WORKSHIFTID = '{0}'", para("WORKSHIFTID"))
                sqlList.Add(strsql.ToString)
                For Each dr As DataRow In dtDetail.Rows
                    strsql.Clear()
                    strsql.AppendLine("INSERT INTO workshiftdetail (WORKSHIFTDETAILID,WORKSHIFTID,STARTDATE,ENDDATE,SEQUENCE,MINS) VALUES(sys_guid(),")
                    strsql.AppendFormat("'{0}',", para("WORKSHIFTID"))
                    strsql.AppendFormat("'{0}',", dr("STARTDATE"))
                    strsql.AppendFormat("'{0}',", dr("ENDDATE"))
                    strsql.AppendFormat("'{0}',", dr("SEQUENCE"))
                    strsql.AppendFormat("'{0}')", dr("MINS"))
                    sqlList.Add(strsql.ToString)
                Next
                DatabaseHelper.ExecuteSqlTran(sqlList) '更新记录'
                result.Message = "更新成功"
                result.Result = True
            Else
                strsql.Clear()
                strsql.AppendFormat("DELETE FROM workshift ws WHERE ws.WORKSHIFTID IN ({0})", para("WORKSHIFTIDS").ToString.Trim(","))
                sqlList.Add(strsql.ToString)
                strsql.Clear()
                strsql.AppendFormat("DELETE FROM workshiftdetail wsd WHERE wsd.WORKSHIFTID IN ({0})", para("WORKSHIFTIDS").ToString.Trim(","))
                sqlList.Add(strsql.ToString)
                DatabaseHelper.ExecuteSqlTran(sqlList) '删除记录'
                result.Message = "删除成功"
                result.Result = True
            End If
        Catch ex As Exception
            result.Message = ex.Message
            result.Result = False
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 验证班次保存
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function IsUserWorkShift(ByVal para As DataRow) As uMESProcessResult
        Dim strsql As New StringBuilder
        Dim result = New uMESProcessResult()
        result.Result = True
        If para("type") = "add" Then
            '查询同名班次
            strsql.AppendFormat("SELECT * FROM workshift ws where ws.workshiftname = '{0}'", para("WORKSHIFTNAME"))
            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
            If dt.Rows.Count > 0 Then
                result.Message = "存在同名的班次"
                result.Result = False
                Return result
                Exit Function
            End If
        ElseIf para("type") = "update" Then
            '查询除了当前班次外的其他同名批次
            strsql.AppendFormat("SELECT * FROM workshift ws where ws.workshiftid <> '{0}' and ws.workshiftname = '{1}'", para("WORKSHIFTID"), para("WORKSHIFTNAME"))
            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
            If dt.Rows.Count > 0 Then
                result.Message = "存在同名的班次"
                result.Result = False
                Return result
                Exit Function
            End If
            strsql.Clear()
            '查询是否关联了设备
            If para("ISUSE") = "1" Then
                strsql.AppendLine("SELECT * FROM resourcedef r")
                strsql.AppendLine(" LEFT JOIN workshift ws  ON ws.workshiftid = r.workshiftid")
                strsql.AppendFormat(" WHERE ws.workshiftid = '{0}'", para("WORKSHIFTID"))
                Dim dt1 As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
                If dt1.Rows.Count > 0 Then
                    result.Message = "该班次存在关联设备，无法更改为不可用"
                    result.Result = False
                    Return result
                    Exit Function
                End If
            End If
        ElseIf para("type") = "delete" Then
            '查询是否关联了设备
            strsql.AppendLine("SELECT ws.workshiftname FROM resourcedef r")
            strsql.AppendLine(" LEFT JOIN workshift ws  ON ws.workshiftid = r.workshiftid")
            strsql.AppendFormat(" WHERE ws.workshiftid IN ({0})", para("WORKSHIFTIDS").ToString.Trim(","))
            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
            If dt.Rows.Count > 0 Then
                'Dim strWorkShiftName = ""
                'For Each dr As DataRow In dt.Rows
                '    strWorkShiftName += "[" + dr("workshiftname").ToString + "]"
                'Next
                'result.Message = "班次" + strWorkShiftName + "关联了设备，无法删除"
                result.Message = "该班次存在关联设备，无法删除"
                result.Result = False
                Return result
                Exit Function
            End If
        Else
            result.Message = "未知操作"
            result.Result = False
        End If
        Return result
    End Function
End Class
